CHARSET utf8;
DROP DATABASE IF EXISTS pronote;
CREATE DATABASE pronote CHARACTER SET = utf8 COLLATE = utf8_general_ci;
USE pronote;
CREATE TABLE IF NOT EXISTS eleve (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
                                  nom VARCHAR(60),
				                  prenom VARCHAR(60),
					              classe VARCHAR(10),
					              naissance DATE,
					              entree DATE,
					              sortie DATE);

INSERT INTO eleve VALUES (1,'Zeblouze', 'Agathe','TG3','2004-11-03','2021-09-03',NULL),
                         (2,'Desartistes','André','1G5','2005-08-27','2021-09-03','2021-10-22'),
                         (3,'Zetaufré', 'Mélanie','2GT6','2006-04-06','2021-09-03',NULL),
                         (4,'Fémal', 'Aïcha','2GT3','2006-04-06','2021-09-17',NULL),
                         (5,'Legnidu', 'Clotaire','2GT3','2005-02-25','2021-09-03',NULL),
                         (6,'Coppi', 'Fausto','2GT3','2006-03-14','2021-09-03','2021-12-18'),
                         (7,'Lovelace', 'Ada','1G2','2005-06-20','2021-09-03',NULL),
                         (8,'Dubois', 'Alix','TG1','2004-01-05','2021-09-03',NULL);

								  
CREATE TABLE IF NOT EXISTS matiere (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
                                    intitule VARCHAR(80));

INSERT INTO matiere VALUES (1,'Mathématiques'),
                           (2,'Physique-Chimie'),
						   (5,'Espagnol'),
						   (17,'Numérique et Sciences de l\'Informatique'),
						   (18,'Sciences Numériques et Technologie');
									
CREATE TABLE IF NOT EXISTS professeur (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
                                       nom VARCHAR(60),
						               prenom VARCHAR(60),
						               id_matiere INT UNSIGNED,
									   FOREIGN KEY (id_matiere) REFERENCES matiere(id) ON DELETE SET NULL ON UPDATE CASCADE);

INSERT INTO professeur VALUES (1,'Tournesol','Tryphon',1),
                              (2,'Ramirez','Pedro',5),
							  (3,'Allister','Marc',2),
							  (4,'Hector','Marie',18),
							  (5,'Babbage','Charles',17);

CREATE TABLE IF NOT EXISTS type_evaluation (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
                                            intitule VARCHAR(80));

INSERT INTO type_evaluation VALUES (1,'Devoir surveillé'),
                                   (2,'QCM noté'),
								   (3,'Devoir maison'),
								   (4,'Interrogation de cours'),
								   (5,'Travaux pratiques'),
								   (6,'Oral'),
								   (7,'Exposé'),
								   (8,'Compréhension orale'),
								   (9,'Compréhension écrite');

CREATE TABLE IF NOT EXISTS evaluation (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
                                       titre VARCHAR(80),
						               coef DECIMAL(5,2),
						               maxi DECIMAL(5,2),
						               date_eval DATE,
						               id_type INT UNSIGNED,
						               trimestre SMALLINT UNSIGNED,
						               id_matiere INT UNSIGNED,
						               id_prof INT UNSIGNED,
									   FOREIGN KEY (id_type) REFERENCES type_evaluation(id) ON DELETE SET NULL ON UPDATE CASCADE,
									   FOREIGN KEY (id_matiere) REFERENCES matiere(id) ON DELETE SET NULL ON UPDATE CASCADE,
									   FOREIGN KEY (id_prof) REFERENCES professeur(id) ON DELETE SET NULL ON UPDATE CASCADE);

INSERT INTO evaluation VALUES (1,'DS Géométrie',2,20,'2021-12-12',1,2,1,1),
                              (2,'Compréhension #3',0.5,10,'2021-10-08',8,1,5,2),
							  (3,'Exposé',3,20,'2021-10-16',7,1,2,3),
							  (4,'Interro #2',1,15,'2021-09-22',4,1,18,4),
							  (5,'TP langages',2,20,'2021-11-24',5,2,17,5);

CREATE TABLE IF NOT EXISTS note (id_eleve INT UNSIGNED NOT NULL,
                                 id_eval INT UNSIGNED NOT NULL,
				                 note DECIMAL(5,2),
				                 symbole VARCHAR(12),
				                 PRIMARY KEY (id_eleve, id_eval),
								 FOREIGN KEY (id_eleve) REFERENCES eleve(id) ON DELETE CASCADE ON UPDATE CASCADE,
								 FOREIGN KEY (id_eval) REFERENCES evaluation(id) ON DELETE CASCADE ON UPDATE CASCADE);

INSERT INTO note VALUES (4,4,12, NULL),
                        (5,4,4, NULL),
						(6,4,11.5, NULL),
						(3,4,NULL,'Abs'),
						(1,1,8.5,NULL),
						(2,2,7,NULL),
						(3,3,17,NULL),
						(7,5,19,NULL);